<?
session_start();
error_reporting(E_ALL);
include('conn.php');

$custid = $_SESSION['custid'];
//$custtype = $_SESSION['custtype'];
//$year = $_REQUEST['year'];  sum(sumpaidamt) as sumpaidamt,

 //$qry1 = "select sum(t.debitamt) - sum(t.creditamt) as outstanding, 
//		  o.custid, c.custdesc as customername, sum(amtreceived) as sumreceivedamt, 
//		  sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)) as remainingamt 
//		  from opbalmast o left join transactionmast t on t.custid = o.custid 
//		  left join customermast c on c.custid = o.custid
//		  group by o.custid,t.custid 
//		  order by o.custid ,t.custid"; 


///$qry1 ="select o.opbaldate,
//IF(c.custdesc = 'YATRA', sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)), NULL) 'YATRA',
//IF(c.custdesc = 'SPICEJET CORP', sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)), NULL) 'SPICEJET CORP',
//IF(c.custdesc = 'INDIGO CORP', sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)), NULL) 'INDIGO CORP'
//from opbalmast o left join transactionmast t on t.custid = o.custid
//left join customermast c on c.custid = o.custid
//group by o.custid,t.custid
//order by o.custid ,t.custid";
//echo $qry1;

//$qry1 ="SET @runtot:=0";
//$qry1 =$qry1."SELECT q1.d, q1.custid, (@runtot := @runtot + q1.c) AS rt FROM (SELECT t1.opbaldate AS d, t1.custid, sum(amt) AS c FROM (select opbaldate,custid, case when amtpaid > 0 then -1 * amtpaid when amtreceived > 0 then amtreceived else 0 end as amt from opbalmast) t1 GROUP BY custid,opbaldate) AS q1";
/* $qry1 ="SELECT q1.d, q1.custid, (@runtot := @runtot + q1.c) AS rt FROM (SELECT t1.opbaldate AS d, t1.custid, sum(amt) AS c FROM (select opbaldate,custid, case when amtpaid > 0 then -1 * amtpaid when amtreceived > 0 then amtreceived else 0 end as amt from opbalmast where custid=466) t1 GROUP BY custid,opbaldate) AS q1";
$qry = mysql_query($qry1);
$data = array();
$qry = mysql_query($qry1);
$data = array();
$i=1; */
/* while($rows = mysql_fetch_array($qry))
{
				$data[] = array("Sr_NO" => $i++,
					  "outstanding" => $rows['outstanding'],
					 "custid" => $rows['custid'],
					 "customername" => $rows['customername'],
                     "sumreceivedamt" => $rows['sumreceivedamt'],	
					 "sumpaidamt" => $rows['sumpaidamt'],	
					 "renamingamt" => $rows['rt']
	          );
}
echo json_encode($data);
?> */

	$qry2 = "SET @runtot=0;";
	mysql_query($qry2);
	
	$qry1="SELECT n.custid,n.opbaldate, SUM(o.order_total) As running_total FROM (SELECT custid, opbaldate , sum(amtreceived)-SUM(amtpaid) As order_total FROM opbalmast GROUP BY custid,opbaldate ORDER BY custid,opbaldate) n INNER JOIN (SELECT custid, opbaldate, sum(amtreceived)-SUM(amtpaid) As order_total FROM opbalmast GROUP BY custid,opbaldate ORDER BY custid,opbaldate) o ON (n.custid = o.custid) and (n.opbaldate >= o.opbaldate) GROUP BY n.custid, n.opbaldate ORDER BY n.custid, n.opbaldate ";
	
	$qry = mysql_query($qry1);
	$data = array();
	$i=1;	
	
	while($rows = mysql_fetch_array($qry))
	{
		$qrycust = "select custdesc from customermast where custid='".$rows['custid']."';";
		$rowcust = mysql_fetch_array(mysql_query($qrycust));
		
		$data[] = array("Sr_NO" => $i++,        			
						"rqdate" => $rows['opbaldate'],
						"custid" => $rows['custid'],
						"customer" => $rowcust['custdesc'],
						"runningtotal" =>$rows['running_total']
						);
	}
	echo json_encode($data);
?>